package cn.tedu.jdbc;

import cn.tedu.jdbc.utils.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 使用JDBC实现员工信息管理
 */
public class JDBCTest24 {
    public static void main(String[] args) {
        System.out.println("欢迎使用员工信息管理，请认真阅读使用说明.");
        System.out.println("本系统的功能主要包括，查看员工列表、查看某个员工详细信息、新增员工、修改员工、删除员工");
        System.out.println("请输入对应的功能编号选择功能：");
        System.out.println("[1]查看员工列表");
        System.out.println("[2]查看某个员工详细信息");
        System.out.println("[3]新增员工");
        System.out.println("[4]修改员工信息");
        System.out.println("[5]删除员工信息");
        System.out.println("[0]退出系统");

        Scanner sc = new Scanner(System.in);
        while(true){
            System.out.println("请输入业务编号：");
            switch (sc.nextInt()){
                case 1:
                    //  查看员工列表
                    System.out.println("员工信息列表如下：");
                    doList();
                    break;
                case 2:
                    //  查看某个员工详细信息
                    doList();
                    System.out.println("请输入员工的id：");
                    long empId = sc.nextLong();
                    System.out.println("员工id[" + empId + "]的详细信息如下：");
                    doDatail(empId);
                    break;
                case 3:
                    //  新增员工
                    System.out.println("请输入新增员工信息：");
                    System.out.println("请输入员工姓名：");
                    String name = sc.next();
                    System.out.println("请输入员工职位：");
                    String job = sc.next();
                    System.out.println("请输入员工入职时间：");
                    String hiredate = sc.next();
                    System.out.println("请输入员工工资：");
                    double salary = sc.nextDouble();
                    System.out.println("请输入员工住址：");
                    String address = sc.next();
                    doSave(name, job, hiredate, salary, address);
                    System.out.println("新增" + name + "信息成功！");
                    doList();
                    break;
                case 4:
                    //  修改员工信息
                    doList();
                    //输入员工id
                    System.out.println("请输入要修改的员工ID：");
                    long id = sc.nextLong();
                    doDatail(id);
                    System.out.println("请输入修改后的员工姓名：");
                    name = sc.next();
                    System.out.println("请输入修改后的员工职位：");
                    job = sc.next();
                    System.out.println("请输入修改后的员工入职时间：");
                    hiredate = sc.next();
                    System.out.println("请输入修改后的员工工资：");
                    salary = sc.nextDouble();
                    System.out.println("请输入修改后的员工住址：");
                    address = sc.next();
                    doModify(id, name, job, hiredate, salary, address);
                    System.out.println("恭喜修改完成，修改后的信息如下：");
                    doList();
                    break;
                case 5:
                    //  删除员工信息
                    doList();
                    System.out.println("请输入要删除的员工id：");
                    id = sc.nextLong();
                    doDelete(id);
                    System.out.println("已删除id为[" + id + "]员工信息");
                    System.out.println("删除后的员工列表信息如下：");
                    doList();
                    break;
                case 0:
                    //  退出系统
                    System.out.println("欢迎下次再来！");
                    System.exit(0);
                default:
                    //  输入功能编号有误
                    System.out.println("输入功能编号有误，请重新输入！");
            }
        }
    }



    private static void doDelete(long id) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "delete from t_employee where id = ? ";
            ps = conn.prepareStatement(sql);
            ps.setLong(1,id);
            int count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
        }

    }

    private static void doModify(long id, String name, String job, String hiredate, double salary, String address) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "update t_employee set name=?,job=?,hiredate=?,salary=?,address=? where id= ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,job);
            ps.setString(3,hiredate);
            ps.setDouble(4,salary);
            ps.setString(5,address);
            ps.setLong(6,id);
            int count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,null);
        }
    }



    private static void doSave(String name, String job, String hiredate, double salary, String address) {
        Connection  conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbUtils.getConnection();
            String sql ="insert into t_employee(name,job,hiredate,salary,address) values(?,?,?,?,?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            ps.setString(2,job);
            ps.setString(3,hiredate);
            ps.setDouble(4,salary);
            ps.setString(5,address);
            int count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,null);
        }
    }

    private static void doDatail(long empId) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "select * from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1,empId);
            // 执行查询语句并获取结果集
            rs = ps.executeQuery();

            if (rs.next()) {
                long id = rs.getLong("id");
                String name = rs.getString("name");
                String job = rs.getString("job");
                String hiredate = rs.getString("hiredate");
                Double salary = rs.getDouble("salary");
                String address = rs.getString("address");
                System.out.println(id + "\t" + name + "\t" + job + "\t" + hiredate + "\t" + salary + "\t" + address);
                System.out.println("---------------------------------");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,rs);
        }
    }

    private static void doList() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbUtils.getConnection();
            String sql = "select id,name,job from t_employee";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                long id = rs.getLong("id");
                String name = rs.getString("name");
                String job = rs.getString("job");
                System.out.println(id + "\t" + name + "\t" + job);
                System.out.println("---------------------------------");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn,ps,rs);
        }

    }


}

